package jsyf.oa.dao.thurTrack;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.com.util.DateUtils;
import jsyf.oa.dao.system.LogsDao;
import jsyf.oa.dao.system.User2RegionDao;
import jsyf.oa.dao.system.UserDao;
import jsyf.oa.entity.thurTrack.ThurTrackEntity;
import jsyf.oa.entity.wreport.WReportCustomerEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.*;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by Maqiuqiu on 2018/8/16.
 */
@Repository
public class ThurTrackDao extends BaseDao {

    @Autowired
    LogsDao logsDao;
    @Autowired
    private UserDao userDao;
    @Autowired
    private User2RegionDao user2RegionDao;

    public ThurTrackDao() {
        super.iBaseEntity = new ThurTrackEntity();
    }

    public List<ThurTrackEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<ThurTrackEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            ThurTrackEntity entity = (ThurTrackEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ThurTrackEntity.class);
    }

    /**
     * 获取用户区域
     * @param userId
     * @return
     */
    public List<JSONObject> getRegionByUserId(String userId) {
        String sql = "select \n" +
                "t_sys_region.ID as regionId,\n" +
                "t_sys_region.NAME as regionName\n" +
                "FROM t_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.PARENT_ID!=0";
        return super.queryForJsonList(sql);
    }

    public List<JSONObject> getThurTrackList(String userId,String condition, Object[] params, Integer requestPage, Integer pageSize){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_rpt_thursday_genzong.ID AS id,\n" +
                "\tt_rpt_thursday_genzong.DAY AS day,\n" +
                "\tt_rpt_thursday_genzong.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\tt_rpt_thursday_genzong.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\tt_rpt_thursday_genzong.AUDIT_FLAG AS auditFlag,\n" +
                "\tt_rpt_thursday_genzong.COMPANY_ID AS companyId,\n" +
                "\tt_rpt_thursday_genzong.COMPANY_NAME AS companyName,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tt_rpt_thursday_genzong.BUILD_CITY_NAME AS buildCityName,\n" +
                "\tt_rpt_thursday_genzong.CONFIRMATION_DATE AS confirmationDate,\n" +
                "\tt_rpt_thursday_genzong.CLOSEORDER_PROBABILITY AS closeorderProbability,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_ID AS customerId,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_TEL AS customerTel,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_NAME AS customerName,\n" +
                "\tt_rpt_thursday_genzong.YHDE_OLDER AS yhdeOlder,\n" +
                "\tt_rpt_thursday_genzong.HAVING_MODEL AS havingModel,\n" +
                "\tt_rpt_thursday_genzong.BUILD_CONDITION AS buildCondition,\n" +
                "\tt_rpt_thursday_genzong.NEED_MODEL_ID AS needModelId,\n" +
                "\tt_rpt_thursday_genzong.NEED_MODEL AS needModel,\n" +
                "\tt_rpt_thursday_genzong.NEED_NUMBER AS needNumber,\n" +
                "\tt_rpt_thursday_genzong.REPLACEMENT_MODEL AS replacementModel,\n" +
                "\tt_rpt_thursday_genzong.COMPETITOR_BRAND AS competitorBrand,\n" +
                "\tt_rpt_thursday_genzong.BUY_TYPE AS buyType,\n" +
                "\tt_rpt_thursday_genzong.CONFIRMATION_STATUS AS confirmationStatus,\n" +
                "\tt_rpt_thursday_genzong.DEALERQUOTE AS dealerquote,\n" +
                "\tt_rpt_thursday_genzong.DEALERPOLICY AS dealerpolicy,\n" +
                "\tt_rpt_thursday_genzong.ACTUAL_DATE AS actualDate,\n" +
                "\tt_rpt_thursday_genzong.YUJI_DELIVERY_DATE AS yujiDeliveryDate,\n" +
                "\tt_rpt_thursday_genzong.SHIJI_LOSE_DATE AS shijiLoseDate,\n" +
                "\tt_rpt_thursday_genzong.DELIVERY_DATE AS deliveryDate,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_MANAGER_NAME AS regionalManagerName,\n" +
                "\tt_rpt_thursday_genzong.BIG_REGIONAL_MANAGER__NAME AS bigRegionalManagerName,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_MANAGER_ID AS regionalManagerId,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_ID AS regionalId,\n" +
                "\tt_rpt_thursday_genzong.BIG_REGIONAL_ID AS bigRegionalId,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_BRAND AS lostorderBrand,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_MODEL AS lostorderModel,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_FLAG AS lostorderFlag,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_REASON AS lostorderReason,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_REASON_DETAILS AS lostorderReasonDetails,\n" +
                "\tt_rpt_thursday_genzong.SALE_PRICE AS salePrice,\n" +
                "\tt_rpt_thursday_genzong.INTEREST_RATE AS interestRate,\n" +
                "\tt_rpt_thursday_genzong.DOWN_PAYMENT AS downPayment,\n" +
                "\tt_rpt_thursday_genzong.REPLACEMENT_SUBSIDY AS replacementSubsidy,\n" +
                "\tt_rpt_thursday_genzong.ACCESSBONUS AS accessbonus,\n" +
                "\tt_rpt_thursday_genzong.OTHER_INFORMATION AS otherInformation,\n" +
                "\tt_rpt_thursday_genzong.INFORMATION_INTEGRITY AS informationIntegrity,\n" +
                "\tt_rpt_thursday_genzong.TRANSACTION_PROBABILITY AS transactionProbability,\n" +
                "\tt_rpt_thursday_genzong.PROVINCE_NAME AS provinceName,\n" +
                "\tt_rpt_thursday_genzong.CITY_NAME AS cityName,\n" +
                "\tt_rpt_thursday_genzong.COUNTY_NAME AS countyName,\n" +
                "\tt_rpt_thursday_genzong.CREATE_TIME AS createTime,\n" +
                "\tt_rpt_thursday_genzong.BRAND_NAME AS brandName,\n" +
                "\tt_rpt_thursday_genzong.BIG_REGIONAL_NAME AS bigRegionalName,\n" +
                "\t\tCASE\n" +
                "WHEN t_rpt_thursday_genzong.AUDIT_FLAG = '-1' THEN\n" +
                "\t1\n" +
                "WHEN t_rpt_thursday_genzong.AUDIT_FLAG = '0'\n" +
                "AND t_rpt_thursday_genzong.CURRENT_AUDITOR_ID = '"+userId+"' THEN\n" +
                "2\n" +
                "ELSE\n" +
                "\t3 \n" +
                "END\n" +
                "AS sortNo\n" +
                "FROM\n" +
                "\tt_rpt_thursday_genzong\n" +
                "WHERE\n" +
                "\t1 = 1 ");
        sql.append(condition);
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString(), params);
    }

    @Override
    public Integer update(IBaseEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.saveLogs(entity);
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }

    @Override
    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.addLogs(entity);
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    /**
     * 将传来日期，得到对应的周四
     *
     * @param date
     * @return
     * @throws Exception
     */
    private String getThursday(String date) throws Exception {
        int nowDay = DateUtils.dayForWeek(date);
        if (3 == nowDay) {
            return date;
        } else {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            Calendar c = Calendar.getInstance();
            c.setTime(format.parse(date));
            c.add(Calendar.DATE, 3 - nowDay);
            return format.format(c.getTime());
        }
    }

    public void insertBatch(List<WReportCustomerEntity> customerList){
        List<LinkedHashMap<String,Object>> insertList = new ArrayList();
        LinkedHashMap<String,Object> obj = null;
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateNowStr = sdf.format(date);
        System.out.println("格式化后的日期：" + dateNowStr);
        for (int i = 0; i < customerList.size(); i++) {
            WReportCustomerEntity customer = customerList.get(i);
            String customerAddress = customer.getProvinceName()+customer.getCityName()+customer.getCountyName();
            obj = new LinkedHashMap<>();
            obj.put("ID",super.generateKey());
            try {
                obj.put("DAY", getThursday(dateNowStr));
            } catch (Exception e) {
                e.printStackTrace();
            }
            obj.put("CURRENT_AUDITOR_ID", "");
            obj.put("CURRENT_AUDITOR_NAME", "");
            obj.put("AUDIT_FLAG", "-1");
            obj.put("COMPANY_ID", userDao.getViewJSONById(customer.getCreateUserId()).getString("companyId"));
            obj.put("COMPANY_NAME", userDao.getViewJSONById(customer.getCreateUserId()).getString("companyName"));
            obj.put("CUSTOMER_ADDRESS", customerAddress);
            obj.put("BUILD_CITY_NAME", "");
            obj.put("CONFIRMATION_DATE", "");
            obj.put("CLOSEORDER_PROBABILITY", "");
            obj.put("CUSTOMER_ID", customer.getId());
            obj.put("CUSTOMER_TEL", customer.getTel());
            obj.put("CUSTOMER_NAME", customer.getName());
            obj.put("YHDE_OLDER", "");
            obj.put("HAVING_MODEL", customer.getExistingDevice());
            obj.put("BUILD_CONDITION", customer.getOtherCondition());
            obj.put("NEED_MODEL_ID", "");
            obj.put("NEED_MODEL", customer.getIntentionModel());
            obj.put("NEED_NUMBER", 1);
            obj.put("REPLACEMENT_MODEL", "");
            obj.put("COMPETITOR_BRAND", customer.getCompetitorCondition());
            obj.put("BUY_TYPE", customer.getBuyType());
            obj.put("CONFIRMATION_STATUS", "");
            obj.put("DEALERQUOTE", "");
            obj.put("DEALERPOLICY", "");
            obj.put("ACTUAL_DATE", "");
            obj.put("YUJI_DELIVERY_DATE", "");
            obj.put("SHIJI_LOSE_DATE", "");
            obj.put("REGIONAL_MANAGER_NAME", userDao.getViewJSONById(customer.getCreateUserId()).getString("name"));
            obj.put("BIG_REGIONAL_MANAGER__NAME", userDao.getViewJSONById(customer.getCreateUserId()).getString("parentName"));
            obj.put("REGIONAL_MANAGER_ID", customer.getCreateUserId());
            obj.put("REGIONAL_ID", "");
            obj.put("BIG_REGIONAL_ID", userDao.getViewJSONById(customer.getCreateUserId()).getString("departId"));
            obj.put("LOSTORDER_BRAND", "");
            obj.put("LOSTORDER_MODEL", "");
            obj.put("LOSTORDER_FLAG", "");
            obj.put("LOSTORDER_REASON", "");
            obj.put("LOSTORDER_REASON_DETAILS", "");
            obj.put("SALE_PRICE", null);
            obj.put("INTEREST_RATE", "");
            obj.put("DOWN_PAYMENT", "");
            obj.put("REPLACEMENT_SUBSIDY", "");
            obj.put("ACCESSBONUS", "");
            obj.put("OTHER_INFORMATION", "");
            obj.put("INFORMATION_INTEGRITY", "");
            obj.put("TRANSACTION_PROBABILITY", "");
            obj.put("DELIVERY_DATE", customer.getBuyDate());
            obj.put("CREATE_TIME", this.getCurrentTime());
            obj.put("UPDATE_TIME", this.getCurrentTime());
            obj.put("PROVINCE_NAME", customer.getProvinceName());
            obj.put("CITY_NAME", customer.getCityName());
            obj.put("COUNTY_NAME",customer.getCountyName());
            obj.put("UPDATE_USER_ID",customer.getCreateUserId());
            insertList.add(obj);
        }
        super.insertBatch("t_rpt_thursday_genzong", insertList);
    }

    public void insertBatchList(List<ThurTrackEntity> thurTrackList){
        List<LinkedHashMap<String,Object>> insertList = new ArrayList();
        LinkedHashMap<String,Object> obj = null;
        for (int i = 0; i < thurTrackList.size(); i++) {
            ThurTrackEntity customer = thurTrackList.get(i);
            obj = new LinkedHashMap<>();
            obj.put("ID", super.generateKey());
            obj.put("DAY", customer.getDay());
            obj.put("CURRENT_AUDITOR_ID", customer.getCurrentAuditId());
            obj.put("CURRENT_AUDITOR_NAME", customer.getCurrentAuditName());
            obj.put("AUDIT_FLAG", customer.getAuditFlag());
            obj.put("COMPANY_ID", customer.getCustomerId());
            obj.put("COMPANY_NAME", customer.getCompanyName());
            obj.put("CUSTOMER_ADDRESS", customer.getCustomerAddress());
            obj.put("BUILD_CITY_NAME", customer.getBuildCityName());
            obj.put("CONFIRMATION_DATE", customer.getConfirmationDate());
            obj.put("CLOSEORDER_PROBABILITY", customer.getCloseorderProbability());
            obj.put("CUSTOMER_ID", customer.getCustomerId());
            obj.put("CUSTOMER_TEL", customer.getCustomerTel());
            obj.put("CUSTOMER_NAME", customer.getCustomerName());
            obj.put("YHDE_OLDER", customer.getYhdeOlder());
            obj.put("HAVING_MODEL", customer.getHavingModel());
            obj.put("BUILD_CONDITION", customer.getBuildCondition());
            obj.put("NEED_MODEL_ID", customer.getNeedModelId());
            obj.put("NEED_MODEL", customer.getNeedModel());
            obj.put("NEED_NUMBER", customer.getNeedNumber());
            obj.put("REPLACEMENT_MODEL", customer.getReplacementModel());
            obj.put("COMPETITOR_BRAND", customer.getCompetitorBrand());
            obj.put("BUY_TYPE", customer.getBuyType());
            obj.put("CONFIRMATION_STATUS", customer.getConfirmationStatus());
            obj.put("DEALERQUOTE", customer.getDealerquote());
            obj.put("DEALERPOLICY", customer.getDealerpolicy());
            obj.put("ACTUAL_DATE", customer.getActualDate());
            obj.put("YUJI_DELIVERY_DATE", customer.getYujiDeliveryDate());
            obj.put("SHIJI_LOSE_DATE", customer.getShijiLoseDate());
            obj.put("REGIONAL_MANAGER_NAME", customer.getRegionalManagerName());
            obj.put("BIG_REGIONAL_MANAGER__NAME", customer.getBigRegionalManagerName());
            obj.put("REGIONAL_MANAGER_ID", customer.getRegionalManagerId());
            obj.put("REGIONAL_ID", customer.getRegionalId());
            obj.put("BIG_REGIONAL_ID", customer.getBigRegionalId());
            obj.put("LOSTORDER_BRAND", customer.getLostorderBrand());
            obj.put("LOSTORDER_MODEL", customer.getLostorderModel());
            obj.put("LOSTORDER_FLAG", customer.getLostorderFlag());
            obj.put("LOSTORDER_REASON", customer.getLostorderReason());
            obj.put("LOSTORDER_REASON_DETAILS", customer.getLostorderReasonDetails());
            obj.put("SALE_PRICE", customer.getSalePrice());
            obj.put("INTEREST_RATE", customer.getInterestRate());
            obj.put("DOWN_PAYMENT", customer.getDownPayment());
            obj.put("REPLACEMENT_SUBSIDY", customer.getReplacementSubsidy());
            obj.put("ACCESSBONUS", customer.getAccessbonus());
            obj.put("OTHER_INFORMATION", customer.getOtherInformation());
            obj.put("INFORMATION_INTEGRITY", customer.getInformationIntegrity());
            obj.put("TRANSACTION_PROBABILITY", customer.getTransactionProbability());
            obj.put("DELIVERY_DATE", customer.getDeliveryDate());
            obj.put("CREATE_TIME", customer.getCreateTime());
            obj.put("UPDATE_TIME", customer.getUpdateTime());
            obj.put("PROVINCE_NAME", customer.getProvinceName());
            obj.put("CITY_NAME", customer.getCityName());
            obj.put("COUNTY_NAME", customer.getCountyName());
            obj.put("UPDATE_USER_ID", customer.getUpdateUserId());
            insertList.add(obj);
        }
        super.insertBatch("t_rpt_thursday_genzong", insertList);
    }

    public JSONObject getTrackDetail(String tel,String userId){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_rpt_thursday_genzong.ID AS id,\n" +
                "\tt_rpt_thursday_genzong. DAY AS `day`,\n" +
                "\tt_rpt_thursday_genzong.CURRENT_AUDITOR_ID AS currentAuditorId,\n" +
                "\tt_rpt_thursday_genzong.CURRENT_AUDITOR_NAME AS currentAuditorName,\n" +
                "\tt_rpt_thursday_genzong.AUDIT_FLAG AS auditFlag,\n" +
                "\tt_rpt_thursday_genzong.COMPANY_ID AS companyId,\n" +
                "\tt_rpt_thursday_genzong.COMPANY_NAME AS companyName,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tt_rpt_thursday_genzong.BUILD_CITY_NAME AS buildCityName,\n" +
                "\tt_rpt_thursday_genzong.CONFIRMATION_DATE AS confirmationDate,\n" +
                "\tt_rpt_thursday_genzong.CLOSEORDER_PROBABILITY AS closeorderProbability,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_ID AS customerId,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_TEL AS customerTel,\n" +
                "\tt_rpt_thursday_genzong.CUSTOMER_NAME AS customerName,\n" +
                "\tt_rpt_thursday_genzong.YHDE_OLDER AS yhdeOlder,\n" +
                "\tt_rpt_thursday_genzong.HAVING_MODEL AS havingModel,\n" +
                "\tt_rpt_thursday_genzong.BUILD_CONDITION AS buildCondition,\n" +
                "\tt_rpt_thursday_genzong.NEED_MODEL_ID AS needModelId,\n" +
                "\tt_rpt_thursday_genzong.NEED_MODEL AS needModel,\n" +
                "\tt_rpt_thursday_genzong.NEED_NUMBER AS needNumber,\n" +
                "\tt_rpt_thursday_genzong.REPLACEMENT_MODEL AS replacementModel,\n" +
                "\tt_rpt_thursday_genzong.COMPETITOR_BRAND AS competitorBrand,\n" +
                "\tt_rpt_thursday_genzong.BUY_TYPE AS buyType,\n" +
                "\tt_rpt_thursday_genzong.CONFIRMATION_STATUS AS confirmationStatus,\n" +
                "\tt_rpt_thursday_genzong.DEALERQUOTE AS dealerquote,\n" +
                "\tt_rpt_thursday_genzong.DEALERPOLICY AS dealerpolicy,\n" +
                "\tt_rpt_thursday_genzong.ACTUAL_DATE AS actualDate,\n" +
                "\tt_rpt_thursday_genzong.YUJI_DELIVERY_DATE AS yujiDeliveryDate,\n" +
                "\tt_rpt_thursday_genzong.SHIJI_LOSE_DATE AS shijiLoseDate,\n" +
                "\tt_rpt_thursday_genzong.DELIVERY_DATE AS deliveryDate,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_MANAGER_NAME AS regionalManagerName,\n" +
                "\tt_rpt_thursday_genzong.BIG_REGIONAL_MANAGER__NAME AS bigRegionalManagerName,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_MANAGER_ID AS regionalManagerId,\n" +
                "\tt_rpt_thursday_genzong.REGIONAL_ID AS regionalId,\n" +
                "\tt_rpt_thursday_genzong.BIG_REGIONAL_ID AS bigRegionalId,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_BRAND AS lostorderBrand,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_MODEL AS lostorderModel,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_FLAG AS lostorderFlag,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_REASON AS lostorderReason,\n" +
                "\tt_rpt_thursday_genzong.LOSTORDER_REASON_DETAILS AS lostorderReasonDetails,\n" +
                "\tt_rpt_thursday_genzong.SALE_PRICE AS salePrice,\n" +
                "\tt_rpt_thursday_genzong.INTEREST_RATE AS interestRate,\n" +
                "\tt_rpt_thursday_genzong.DOWN_PAYMENT AS downPayment,\n" +
                "\tt_rpt_thursday_genzong.REPLACEMENT_SUBSIDY AS replacementSubsidy,\n" +
                "\tt_rpt_thursday_genzong.ACCESSBONUS AS accessbonus,\n" +
                "\tt_rpt_thursday_genzong.OTHER_INFORMATION AS otherInformation,\n" +
                "\tt_rpt_thursday_genzong.INFORMATION_INTEGRITY AS informationIntegrity,\n" +
                "\tt_rpt_thursday_genzong.TRANSACTION_PROBABILITY AS transactionProbability,\n" +
                "\tt_rpt_thursday_genzong.PROVINCE_NAME AS provinceName,\n" +
                "\tt_rpt_thursday_genzong.CITY_NAME AS cityName,\n" +
                "\tt_rpt_thursday_genzong.COUNTY_NAME AS countyName,\n" +
                "\tt_rpt_thursday_genzong.CREATE_TIME AS createTime\n" +
                "FROM\n" +
                "\tt_rpt_thursday_genzong\n" +
                "WHERE\n" +
                "\tCUSTOMER_TEL = '"+tel+"'\n" +
                "AND REGIONAL_MANAGER_ID = '"+userId+"'\n" +
                "ORDER BY\n" +
                "INFORMATION_INTEGRITY desc,\n" +
                "\tCREATE_TIME desc" +
                "\tLIMIT 1 ");
       return super.queryForJsonObject(sql.toString());
    }

    public List<ThurTrackEntity> getAllThurTrackList(String condition){
        StringBuffer sql = new StringBuffer();
        sql.append("   SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t`t_rpt_thursday_genzong`\n" +
                "WHERE 1 = 1 ");
        sql.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        List<ThurTrackEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            ThurTrackEntity entity = (ThurTrackEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    public List<JSONObject> getCurrMonthDeal(String userId, String dateTime){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tv1.`name`,\n" +
                "\tv1.companyName,\n" +
                "\tv1.roleName,\n" +
                "\tv1.departName,\n" +
                "\tt4.*\n" +
                "FROM\n" +
                "\tv_sys_user AS v1\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt3.bigRegionManagerId,\n" +
                "\t\tt3.bigRegionManagerName,\n" +
                "\t\tsum(t3.needNumber) AS currentMonthTarget,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t3.transactionProbability = '50%' THEN\n" +
                "\t\t\t\t0.50\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS percent50,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t3.transactionProbability = '75%' THEN\n" +
                "\t\t\t\t0.75\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS percent75,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t3.transactionProbability = '100%' THEN\n" +
                "\t\t\t\t1.00\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS percent100,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t3.transactionProbability = '50%' THEN\n" +
                "\t\t\t\t0.50\n" +
                "\t\t\tWHEN t3.transactionProbability = '75%' THEN\n" +
                "\t\t\t\t0.75\n" +
                "\t\t\tWHEN t3.transactionProbability = '100%' THEN\n" +
                "\t\t\t\t1.00\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS total\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt2.ID AS id,\n" +
                "\t\t\t\tt1.departName,\n" +
                "\t\t\t\tt1. NAME AS regionManagerName,\n" +
                "\t\t\t\tt1.id AS bigRegionManagerId,\n" +
                "\t\t\t\tt2.BIG_REGIONAL_MANAGER__NAME AS bigRegionManagerName,\n" +
                "\t\t\t\tt2.REGIONAL_MANAGER_ID AS regionManagerId,\n" +
                "\t\t\t\tt2.CUSTOMER_NAME AS customerName,\n" +
                "\t\t\t\tt2.TRANSACTION_PROBABILITY AS transactionProbability,\n" +
                "\t\t\t\tt2.DELIVERY_DATE AS deliveryDate,\n" +
                "\t\t\t\tt2.CONFIRMATION_STATUS AS confirmationStatus,\n" +
                "\t\t\t\tt2.NEED_NUMBER AS needNumber\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tv_sys_user AS t1,\n" +
                "\t\t\t\tt_rpt_thursday_genzong AS t2\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t1.id = '"+userId+"'\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt2.ID IN (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_audit\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\tAUDITOR_ID = t1.id\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tOR t2.ID IN (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_copier\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\tCOPIER_ID = t1.id\n" +
                "\t\t\t\t)\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt2.CONFIRMATION_STATUS NOT LIKE '%丢单%'\n" +
                "\t\t\t\tAND t2.CONFIRMATION_STATUS NOT LIKE '%已交车%'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t2.DELIVERY_DATE LIKE '%"+dateTime+"%'\n" +
                "\t\t) AS t3\n" +
                ") AS t4 ON v1.id = t4.bigRegionManagerId\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND jobState != '1'\n" +
                "AND roleName = '大区经理'\n" +
                "AND companyId IN (\n" +
                "\tSELECT\n" +
                "\t\tcompanyId\n" +
                "\tFROM\n" +
                "\t\tv_sys_user\n" +
                "\tWHERE\n" +
                "\t\tid = '"+userId+"'\n" +
                ")\n" +
                "GROUP BY\n" +
                "\tid\n" +
                "ORDER BY\n" +
                "\tcompanyId,\n" +
                "\tdepartSortNo,\n" +
                "\t`name` ");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getAccuracyRate(String userId,String firstMonth,String secondMonth,String threeMonth){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.companyName,\n" +
                "\tthur.*\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt1.companyName,\n" +
                "\t\tt1.companyId,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.transactionProbability = '50%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+firstMonth+"%' THEN\n" +
                "\t\t\t\t0.50\n" +
                "\t\t\tWHEN t1.transactionProbability = '75%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+firstMonth+"%' THEN\n" +
                "\t\t\t\t0.75\n" +
                "\t\t\tWHEN t1.transactionProbability = '100%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+firstMonth+"%' THEN\n" +
                "\t\t\t\t1.00\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS forcastCountFirst,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.transactionProbability = '50%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+secondMonth+"%' THEN\n" +
                "\t\t\t\t0.50\n" +
                "\t\t\tWHEN t1.transactionProbability = '75%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+secondMonth+"%' THEN\n" +
                "\t\t\t\t0.75\n" +
                "\t\t\tWHEN t1.transactionProbability = '100%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+secondMonth+"%' THEN\n" +
                "\t\t\t\t1.00\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS forcastCountSecond,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.transactionProbability = '50%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+threeMonth+"%' THEN\n" +
                "\t\t\t\t0.50\n" +
                "\t\t\tWHEN t1.transactionProbability = '75%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+threeMonth+"%' THEN\n" +
                "\t\t\t\t0.75\n" +
                "\t\t\tWHEN t1.transactionProbability = '100%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%已交车%'\n" +
                "\t\t\tAND t1.confirmationStatus NOT LIKE '%丢单%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+threeMonth+"%' THEN\n" +
                "\t\t\t\t1.00\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS forcastCountThree,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.confirmationStatus LIKE '%已交车%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+firstMonth+"%' THEN\n" +
                "\t\t\t\t1\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS realSaleCountFirst,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.confirmationStatus LIKE '%已交车%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+secondMonth+"%' THEN\n" +
                "\t\t\t\t1\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS realSaleCountSecond,\n" +
                "\t\tsum(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN t1.confirmationStatus LIKE '%已交车%'\n" +
                "\t\t\tAND t1.deliveryDate LIKE '%"+threeMonth+"%' THEN\n" +
                "\t\t\t\t1\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND\n" +
                "\t\t) AS realSaleCountThree\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tCOMPANY_ID AS companyId,\n" +
                "\t\t\t\tCOMPANY_NAME AS companyName,\n" +
                "\t\t\t\tDELIVERY_DATE AS deliveryDate,\n" +
                "\t\t\t\tCUSTOMER_NAME AS customerName,\n" +
                "\t\t\t\tNEED_NUMBER AS needMumber,\n" +
                "\t\t\t\t`DAY` AS `day`,\n" +
                "\t\t\t\tTRANSACTION_PROBABILITY AS transactionProbability,\n" +
                "\t\t\t\tCONFIRMATION_STATUS AS confirmationStatus\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_rpt_thursday_genzong\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tID IN (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_audit\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\tAUDITOR_ID = '"+userId+"'\n" +
                "\t\t\t\t\tAND AUDIT_FLAG = '1'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tOR ID IN (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_copier\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\tCOPIER_ID = '"+userId+"'\n" +
                "\t\t\t\t\tAND AUDIT_FLAG = '1'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tDELIVERY_DATE LIKE '%"+firstMonth+"%'\n" +
                "\t\t\t\tOR DELIVERY_DATE LIKE '%"+secondMonth+"%'\n" +
                "\t\t\t\tOR DELIVERY_DATE LIKE '%"+threeMonth+"%'\n" +
                "\t\t\t)\n" +
                "\t\t) AS t1\n" +
                ") AS thur ON thur.companyId = v_sys_user.companyId\n" +
                "WHERE\n" +
                "\tv_sys_user.id = '"+userId+"'\n" +
                "GROUP BY\n" +
                "\tv_sys_user.companyId\n" +
                "ORDER BY\n" +
                "\tv_sys_user.companySortNo ");
        return super.queryForJsonList(sql.toString());
    }
}

